﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DPaper
    {
        #region SQL语句
        private const string SQL_INSERT = "insert into Paper(PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated,IsAllowComment) values (@PaperId,@UserPassportId,@Subject,@Body,@Type,@TotalView,@TotalReplay,@IsTop,@isFamousRegion,@Status,@Bak1,@Bak2,@Bak3,@LastUpdate,@DateCreated,@IsAllowComment)";

        private const string SQL_UPDATE = "update Paper set UserPassportId=@UserPassportId,Subject=@Subject,Body=@Body,Type=@Type,TotalView=@TotalView,TotalReplay=@TotalReplay,IsTop=@IsTop,isFamousRegion=@isFamousRegion,Status=@Status,Bak1=@Bak1,Bak2=@Bak2,Bak3=@Bak3,LastUpdate=@LastUpdate,DateCreated=@DateCreated,IsAllowComment=@IsAllowComment where PaperId=@PaperId";

        private const string SQL_DELETE = "delete from Paper where PaperId=@PaperId ";

        private const string SQL_SELECT_ID = "select  PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated,IsAllowComment from Paper where PaperId=@PaperId ";

        private const string SQL_SELECT = "select  PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated,IsAllowComment from Paper";

        private const string SQL_SELECT_POLICY = "select  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join PolicyPaper pp On p.PaperId=pp.PaperId where Status=1 order by p.DateCreated desc";

        private const string SQL_SELECT_POLICY_TOP10 = "select top(10)  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join PolicyPaper pp On p.PaperId=pp.PaperId where Status=1 order by p.DateCreated desc";

        private const string SQL_SELECT_USERRELATION = "select  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join RelationPaper rp On p.PaperId=rp.PaperId where Status=1 order by p.DateCreated desc";

        private const string SQL_SELECT_USERRELATION_TOP10 = "select top(10)  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join RelationPaper rp On p.PaperId=rp.PaperId where Status=1 order by p.DateCreated desc";

        private const string SQL_SELECT_REGIONCATEGORY = "select  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join FamousRegionPaper fr On p.PaperId=fr.PaperId where Status=1 and RegionCategoryId=@RegionCategoryId order by p.DateCreated desc";

        private const string SQL_SELECT_REGIONCATEGORY_TOP10 = "select top(10)  p.PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,p.DateCreated,IsAllowComment from Paper p inner join FamousRegionPaper fr On p.PaperId=fr.PaperId where Status=1 and RegionCategoryId=@RegionCategoryId order by p.DateCreated desc";

        private const string SQL_SELECT_PAPER_BY_CATEGORY_TYPE = "select  p.PaperId,p.UserPassportId,p.Subject,p.Body,p.Type,p.TotalView,p.TotalReplay,p.IsTop,p.isFamousRegion,p.Status,p.Bak1,p.Bak2,p.Bak3,p.LastUpdate,p.DateCreated,IsAllowComment from Paper p,FamousRegionPaper f1,FamousRegion f2,RegionCategory r where r.CategoryType = @CategoryType and r.FamousRegionId = f2.FamousRegionId and f2.Bak1 is null and r.RegionCategoryId = f1.RegionCategoryId and f1.PaperId = p.PaperId and p.Status=1 order by p.Subject, p.DateCreated desc";

        #endregion 

        #region 公共接口

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userProfileInfo"></param>
        /// <returns></returns>
        public bool Insert(PaperInfo paperInfo)
        {
            if (paperInfo == null)
                throw new ArgumentNullException("paperInfo");

            SqlParameter[] parms = GetInsertParms(paperInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt64(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="paperInfo"></param>
        /// <returns></returns>
        public bool Update(PaperInfo paperInfo)
        {
            if (paperInfo == null || paperInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("paperInfo");

            SqlParameter[] parms = GetInsertParms(paperInfo);
            parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="paperId"></param>
        /// <returns></returns>
        public int Delete(long paperId)
        {
            SqlParameter[] parms = {
                    new SqlParameter("@PaperId",SqlDbType.BigInt)
                };
            parms[0].Value = paperId;

            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, SQL_DELETE, parms);

        }

        /// <summary>
        /// 根据文章Id获取文章
        /// </summary>
        /// <param name="paperId"></param>
        /// <returns></returns>
        public PaperInfo GetPaperInfo(long paperId)
        {
            PaperInfo paperInfo = new PaperInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@PaperId",SqlDbType.BigInt)
                };
            parms[0].Value = paperId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, parms))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);

                }
            }
            return paperInfo;
        }

        /// <summary>
        /// 获取所有文章
        /// </summary>
        /// <returns></returns>
        public List<PaperInfo> GetAllPage()
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ID, null))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }
        /// <summary>
        /// 获取所有政策文件
        /// </summary>
        /// <returns></returns>
        public List<PaperInfo> GetAllPolicyPage()
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_POLICY, null))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }
        /// <summary>
        /// 获取前10条政策文件
        /// </summary>
        /// <returns></returns>
        public List<PaperInfo> GetAllPolicyPageTop10()
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_POLICY_TOP10, null))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }

        /// <summary>
        /// 获取所有会员动态文件
        /// </summary>
        /// <returns></returns>
        public List<PaperInfo> GetAllUserRelationPage()
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERRELATION, null))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }
        /// <summary>
        /// 获取前10条会员动态文件
        /// </summary>
        /// <returns></returns>
        public List<PaperInfo> GetAllUserRelationPageTop10()
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USERRELATION_TOP10, null))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }

        /// <summary>
        /// 根据RegionCategoryId 获取相应的文章
        /// </summary>
        /// <param name="regionCategoryId"></param>
        /// <returns></returns>
        public List<PaperInfo> GetAllRegionCategoryPage(long regionCategoryId)
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@RegionCategoryId",SqlDbType.BigInt)
                };
            parms[0].Value = regionCategoryId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_REGIONCATEGORY, parms))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }
        /// <summary>
        /// 根据RegionCategoryId 获取相应的前10条文章
        /// </summary>
        /// <param name="regionCategoryId"></param>
        /// <returns></returns>
        public List<PaperInfo> GetAllRegionCategoryTop10(long regionCategoryId)
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@RegionCategoryId",SqlDbType.BigInt)
                };
            parms[0].Value = regionCategoryId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_REGIONCATEGORY_TOP10, parms))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }

        public List<PaperInfo> GetPapersByCategoryType(int CategoryType)
        {
            List<PaperInfo> pagers = new List<PaperInfo>();

            PaperInfo paperInfo = null;
            SqlParameter[] parms = {
                    new SqlParameter("@CategoryType",SqlDbType.Int)
                };
            parms[0].Value = CategoryType;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_PAPER_BY_CATEGORY_TYPE, parms))
            {
                while (reader.Read())
                {
                    paperInfo = GetInstanceByDataReader(reader);
                    if (paperInfo != null && paperInfo.PaperId != 0)
                        pagers.Add(paperInfo);
                }
            }
            return pagers;
        }
        #endregion

        #region 私有接口


        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }

        private SqlParameter[] GetInsertParms(PaperInfo paperInfo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@PaperId", SqlDbType.BigInt,8),
					new SqlParameter("@UserPassportId", SqlDbType.BigInt,8),
					new SqlParameter("@Subject", SqlDbType.NVarChar,100),
					new SqlParameter("@Body", SqlDbType.NVarChar,2147483640),
					new SqlParameter("@Type", SqlDbType.Int,4),
					new SqlParameter("@TotalView", SqlDbType.Int,4),
					new SqlParameter("@TotalReplay", SqlDbType.Int,4),
					new SqlParameter("@IsTop", SqlDbType.Int,4),
					new SqlParameter("@isFamousRegion", SqlDbType.Int,4),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Bak1", SqlDbType.VarChar,20),
					new SqlParameter("@Bak2", SqlDbType.VarChar,20),
					new SqlParameter("@Bak3", SqlDbType.VarChar,20),
					new SqlParameter("@LastUpdate", SqlDbType.DateTime),
					new SqlParameter("@DateCreated", SqlDbType.DateTime),//,IsAllowComment
                    new SqlParameter("@IsAllowComment", SqlDbType.Int,4)                    };




            return SetInsertParms(parameters, paperInfo);
        }

        private SqlParameter[] SetInsertParms(SqlParameter[] parameters, PaperInfo paperInfo)
        {
            parameters[0].Value = paperInfo.PaperId;
            parameters[1].Value = paperInfo.UserPassportId;
            parameters[2].Value = paperInfo.Subject;
            parameters[3].Value = paperInfo.Body;
            parameters[4].Value = paperInfo.Type;
            parameters[5].Value = paperInfo.TotalView;
            parameters[6].Value = paperInfo.TotalReplay;
            parameters[7].Value = paperInfo.IsTop;
            parameters[8].Value = paperInfo.isFamousRegion;
            parameters[9].Value = paperInfo.Status;
            parameters[10].Value = paperInfo.Bak1;
            parameters[11].Value = paperInfo.Bak2;
            parameters[12].Value = paperInfo.Bak3;
            parameters[13].Value = paperInfo.LastUpdate;
            parameters[14].Value = paperInfo.DateCreated;
            parameters[15].Value = paperInfo.IsAllowComment;


            return parameters;
        }


        private PaperInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            PaperInfo paperInfo = new PaperInfo();
            //PaperId,UserPassportId,Subject,Body,Type,TotalView,TotalReplay,IsTop,isFamousRegion,Status,Bak1,Bak2,Bak3,LastUpdate,DateCreated

            paperInfo.PaperId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            paperInfo.UserPassportId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1);
            paperInfo.Subject = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
            paperInfo.Body = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
            paperInfo.Type = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);
            paperInfo.TotalView = reader.IsDBNull(5) ? 0 : reader.GetInt32(5);
            paperInfo.TotalReplay = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
            paperInfo.IsTop = reader.IsDBNull(7) ? 0 : reader.GetInt32(7);
            paperInfo.isFamousRegion = reader.IsDBNull(8) ? 0 : reader.GetInt32(8);
            paperInfo.Status = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
            paperInfo.Bak1 = reader.IsDBNull(10) ? string.Empty : reader.GetString(10);
            paperInfo.Bak2 = reader.IsDBNull(11) ? string.Empty : reader.GetString(11);
            paperInfo.Bak3 = reader.IsDBNull(12) ? string.Empty : reader.GetString(12);
            paperInfo.LastUpdate = reader.IsDBNull(13) ? DateTime.MinValue : reader.GetDateTime(13);
            paperInfo.DateCreated = reader.IsDBNull(14) ? DateTime.MinValue : reader.GetDateTime(14);
            paperInfo.IsAllowComment = reader.IsDBNull(15) ? 0 : reader.GetInt32(15);
            return paperInfo;
        }

        #endregion


    }
}
